<?php

use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;

class Lib_XlsxExport
{
    private static $obj;

    private function __construct()
    { }

    /**
     * Undocumented function
     *
     * @param string $savePath
     * @return Lib_XlsxExport
     */
    public static function getInstance(string $savePath = '')
    {
        if (is_null(self::$obj)) {
            self::$obj = new self($savePath);
        }

        return self::$obj;
    }

    public function __clone()
    { }

    /**
     * 导出  excel
     *
     * @param array $exportData  需要导出的数据
     * @return void
     */
    public function execute(array $exportData)
    {
        $spreadsheet = new Spreadsheet();
        try {
            // 设置xlsx 属性
            $spreadsheet->getProperties()
                ->setCreator('system auto create')
                ->setLastModifiedBy('system auto create')
                ->setTitle('Office 2007 XLSX  Document')
                ->setSubject('Office 2007 XLSX  Document')
                ->setDescription('Test document for Office 2007 XLSX, generated using PHP classes.')
                ->setKeywords('office 2007 openxml php')
                ->setCategory('Test result file');

            // 设置表头
            $spreadsheet->setActiveSheetIndex(0)
                ->setCellValue('A1', '编号')
                ->setCellValue('B1', '接收时间')
                ->setCellValue('C1', '案号')
                ->setCellValue('D1', '合同编号')
                ->setCellValue('E1', '当事人姓名')
                ->setCellValue('F1', '修复成功')
                ->setCellValue('G1', '调解结果')
                ->setCellValue('H1', '调解标的（单位：元)')
                ->setCellValue('I1', '到账金额')
                ->setCellValue('J1', '欠还本金')
                ->setCellValue('K1', '欠还利息')
                ->setCellValue('L1', '逾期时间 (单位：天)')
                ->setCellValue('M1', '最近更新时间')
                ->setCellValue('N1', '调解员')
                ->setCellValue('O1', '原告')
                ->setCellValue('P1', '有效通话')
                ->setCellValue('Q1', '首次拨打时间')
                ->setCellValue('R1', '案件超期时间')
                ->setCellValue('S1', '管辖法院')
                ->setCellValue('T1', '电子送达')
                ->setCellValue('U1', '批次号')
                ->setCellValue('v1', '备注');


            // 添加表格数据
            $total_amount = 0;
            $total_repay_amount = 0;
            $count = count($exportData);
            if (!empty($exportData)) {
                foreach ($exportData as $k => $item) {
                    $index = $k + 2;
                    $total_amount += $item['QKZE'];
                    $total_repay_amount += $item['total_repay_amount'];
                    $spreadsheet->setActiveSheetIndex(0)
                        ->setCellValue('A' . $index, $k+1)
                        ->setCellValue('B' . $index, date("Y/m/d",$item['add_time']))
                        ->setCellValue('C' . $index, $item['mediation_code'] ? $item['mediation_code'] : $item['zxah'])
                        ->setCellValueExplicit('D' . $index, $item['HTMCBH'],'s')
                        ->setCellValue('E' . $index, $item['defendant_name'] ?? "")
                        ->setCellValue('F' . $index, $item['repair_status'] == 3 || $item['repair_status'] == 5 ? "成功" : "失败")
                        ->setCellValue('G' . $index, $item['mediate_status'])
                        ->setCellValue('H' . $index, $item['QKZE'])
                        ->setCellValue('I' . $index, $item['total_repay_amount'])
                        ->setCellValue('J' . $index, $item['QHBJ'])
                        ->setCellValue('K' . $index, $item['QHLX'])
                        ->setCellValue('L' . $index, $item['YQSJ'])
                        ->setCellValue('M' . $index, date("Y/m/d H:i:s",$item['update_time']))
                        ->setCellValue('N' . $index, $item['mediator'])
                        ->setCellValue('O' . $index, $item['plaintiff_name'])
                        ->setCellValue('P' . $index, $item['valid_call'] == 1 ? "是" : "否")
                        ->setCellValue('Q' . $index, date("Y/m/d",$item['first_call_time']))
                        ->setCellValue('R' . $index, date("Y/m/d",$item['mediate_expire_time']))
                        ->setCellValue('S' . $index, $item['court'])
                        ->setCellValue('T' . $index, $item['ele_service'] == 1 ? "是" : "否")
                        ->setCellValue('U' . $index, $item['batch_num'])
                        ->setCellValue('v' . $index, $item['desc']);

                }
                $index = $count+3;
                $spreadsheet->setActiveSheetIndex(0)
                    ->setCellValue('I' . $index, '案件总数：'.$count)
                    ->setCellValue('J' . $index, '案件欠款总额：'.$total_amount)
                    ->setCellValue('K' . $index, '案件到账总额：'.$total_repay_amount);
            }



            // 重命名表格tab
            $spreadsheet->getActiveSheet()->setTitle('Simple');

            // Set active sheet index to the first sheet, so Excel opens this as the first sheet
            $spreadsheet->setActiveSheetIndex(0);
            // Redirect output to a client’s web browser (Xlsx)
            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
            header('Content-Disposition: attachment;filename="' . date('Y-m-d H:i:s') . '.xlsx"');
            header('Cache-Control: max-age=0');
            // If you're serving to IE 9, then the following may be needed
            header('Cache-Control: max-age=1');
            // If you're serving to IE over SSL, then the following may be needed
            header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
            header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
            header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
            header('Pragma: public'); // HTTP/1.0
            $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
            $writer->save('php://output');



            // $writer = new Xlsx($spreadsheet);
            // $pathArr = Lib_UnzipPackage::getInstance()->getPathArr();
            // $realP = $pathArr[1]  . date('YmdHis') . '_log.csv';
            // $writer->save($pathArr[0] . $realP);
        } catch (Throwable $th) {
            echo $th->getMessage() . PHP_EOL . $th->getLine() . PHP_EOL . $th->getFile();
        }
    }

    /**
     * 手机信息修复文件下载
     */
    public function downExcel(array $exportData, $header,$fileName='')
    {
        $spreadsheet = new Spreadsheet();
        try {
            // 设置xlsx 属性
            $spreadsheet->getProperties()
                ->setCreator('system auto create')
                ->setLastModifiedBy('system auto create')
                ->setTitle('Office 2007 XLSX  Document')
                ->setSubject('Office 2007 XLSX  Document')
                ->setDescription('Test document for Office 2007 XLSX, generated using PHP classes.')
                ->setKeywords('office 2007 openxml php')
                ->setCategory('Test result file');

            $header_tag = 'A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,AA,AB,AC,AD,AE';
            // 设置表头
            $num = count($header);
            $_header_tag = explode(',', $header_tag);

            $obj = $spreadsheet->setActiveSheetIndex(0);
            $prefix = array_slice($_header_tag,0,$num);
            // echo "<pre/>";
            foreach ($prefix as $_k => $_v) {
                $_prefix = $_v . '1';
                $value = isset($header[$_k]) ? $header[$_k] : '';
                $obj->setCellValue($_prefix, $value);
            }

            // 添加表格数据
            if (!empty($exportData)) {
                $obj_value = $spreadsheet->setActiveSheetIndex(0);
                foreach ($exportData as $k => $item) {
                    $index = $k + 2;
                    $value = array_values($item);
                    $keys = array_keys($item);
                    foreach ($prefix as $_i => $_prefix) {
                        if(is_numeric($value[$_i])){
                            $obj_value->setCellValueExplicit($_prefix . $index, isset($value[$_i]) ? $value[$_i] : '',\PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);
                        } else {
                            $obj_value->setCellValue($_prefix . $index, isset($value[$_i]) ? $value[$_i] : '');
                        }
                        
                    }
                }
            }
            // 重命名表格tab
            $spreadsheet->getActiveSheet()->setTitle('Simple');

            // Set active sheet index to the first sheet, so Excel opens this as the first sheet
            $spreadsheet->setActiveSheetIndex(0);
            // Redirect output to a client’s web browser (Xlsx)
            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
            header('Content-Disposition: attachment;filename="' .$fileName.date('Y-m-dHis') . '.xlsx"');
            header('Cache-Control: max-age=0');
            // If you're serving to IE 9, then the following may be needed
            header('Cache-Control: max-age=1');
            // If you're serving to IE over SSL, then the following may be needed
            header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
            header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
            header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
            header('Pragma: public'); // HTTP/1.0
            $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
            $writer->save('php://output');
        } catch (Throwable $th) {
            echo $th->getMessage() . PHP_EOL . $th->getLine() . PHP_EOL . $th->getFile();
        }

    }
    public function MakeExcel(array $exportData, $header, $path )
    {
        $spreadsheet = new Spreadsheet();
        try {
            // 设置xlsx 属性
            $spreadsheet->getProperties()
                ->setCreator('system auto create')
                ->setLastModifiedBy('system auto create')
                ->setTitle('Office 2007 XLSX  Document')
                ->setSubject('Office 2007 XLSX  Document')
                ->setDescription('Test document for Office 2007 XLSX, generated using PHP classes.')
                ->setKeywords('office 2007 openxml php')
                ->setCategory('Test result file');

            $header_tag = 'A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y,Z,AA,AB,AC,AD,AE';
            // 设置表头
            $num = count($header);
            $_header_tag = explode(',', $header_tag);

            $obj = $spreadsheet->setActiveSheetIndex(0);
            $prefix = array_slice($_header_tag,0,$num);
            // echo "<pre/>";
            foreach ($prefix as $_k => $_v) {
                $_prefix = $_v . '1';
                $value = isset($header[$_k]) ? $header[$_k] : '';
                $obj->setCellValue($_prefix, $value);
            }

            // 添加表格数据
            if (!empty($exportData)) {
                $obj_value = $spreadsheet->setActiveSheetIndex(0);
                foreach ($exportData as $k => $item) {
                    $index = $k + 2;
                    $value = array_values($item);
                    $keys = array_keys($item);
                    foreach ($prefix as $_i => $_prefix) {
                        if(is_numeric($value[$_i])){
                            $obj_value->setCellValueExplicit($_prefix . $index, isset($value[$_i]) ? $value[$_i] : '',\PhpOffice\PhpSpreadsheet\Cell\DataType::TYPE_STRING);
                        } else {
                            $obj_value->setCellValue($_prefix . $index, isset($value[$_i]) ? $value[$_i] : '');
                        }

                    }

                    // $spreadsheet->setActiveSheetIndex(0)
                    //     ->setCellValue('A' . $index, $item['id'] ?? "")
                    //     ->setCellValue('B' . $index, $item['name'] ?? "")
                    //     ->setCellValue('C' . $index, $item['cell'] ?? "");
                    // if ($is_template) {
                    //     $spreadsheet->setActiveSheetIndex(0)->setCellValue('D' . $index, $item['status'] ?? "");
                    // }
                }
            }
            // 重命名表格tab
            $spreadsheet->getActiveSheet()->setTitle('Simple');

            // Set active sheet index to the first sheet, so Excel opens this as the first sheet
            $spreadsheet->setActiveSheetIndex(0);
            // Redirect output to a client’s web browser (Xlsx)
            header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
            header('Content-Disposition: attachment;filename="' . date('Y-m-d H:i:s') . '.xlsx"');
            header('Cache-Control: max-age=0');
            // If you're serving to IE 9, then the following may be needed
            header('Cache-Control: max-age=1');
            // If you're serving to IE over SSL, then the following may be needed
            header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
            header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
            header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
            header('Pragma: public'); // HTTP/1.0
            $writer = IOFactory::createWriter($spreadsheet, 'Xlsx');
            $writer->save($path);

            // $writer = new Xlsx($spreadsheet);
            // $pathArr = Lib_UnzipPackage::getInstance()->getPathArr();
            // $realP = $pathArr[1]  . date('YmdHis') . '_log.csv';
            // $writer->save($pathArr[0] . $realP);
        } catch (Throwable $th) {
            echo $th->getMessage() . PHP_EOL . $th->getLine() . PHP_EOL . $th->getFile();
        }

    }
}
